package com.xd.study.activity;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.xd.study.R;
import com.xd.study.utility.MySQLiteOpenHelper;

/**
 * Created by DGF on 2015/5/4.
 */
public class SqliteActivity extends Activity {

    private SQLiteDatabase database;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.sqlite);

        Button btnCreateDb = (Button)this.findViewById(R.id.sqlite_btn_create_database);
        Button btnCreateTable = (Button)this.findViewById(R.id.sqlite_btn_create_table);
        Button btnInsert = (Button)this.findViewById(R.id.sqlite_btn_insert);
        Button btnUpdate = (Button)this.findViewById(R.id.sqlite_btn_update);
        Button btnQuery = (Button)this.findViewById(R.id.sqlite_btn_query);
        Button btnDelete = (Button)this.findViewById(R.id.sqlite_btn_delete);
        Button btnCloseDb = (Button)this.findViewById(R.id.sqlite_btn_close_database);
        Button btnDeleteDb = (Button)this.findViewById(R.id.sqlite_btn_delete_db);

        /**
         * 创建数据库
         */
        btnCreateDb.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //database = openOrCreateDatabase ("user.db", MODE_PRIVATE, null);
                MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(SqliteActivity.this, "user.db");
                database = mySQLiteOpenHelper.getWritableDatabase();
                Toast.makeText(SqliteActivity.this, "创建数据库成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 创建表格
         * 若使用适配器加载数据库中的数据，要求自增记录id的名称必须为 _id
         */
        btnCreateTable.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database.execSQL("create table if not exists usertb (_id integer primary key autoincrement, name text not null, age integer not null, sex text not null);");
                Toast.makeText(SqliteActivity.this, "创建数据表成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 插入数据
         */
        btnInsert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database.execSQL("insert into usertb(name, age, sex) values('July',18,'woman');");
                database.execSQL("insert into usertb(name, age, sex) values('Joy',28,'man');");

                ContentValues contentValues = new ContentValues();
                contentValues.put("name", "Lucy");
                contentValues.put("age", 20);
                contentValues.put("sex", "woman");
                database.insert("usertb", null, contentValues);

                Toast.makeText(SqliteActivity.this, "插入数据成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 更新数据
         */
        btnUpdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database.execSQL("update usertb set age=30 where name='Lucy';");

                ContentValues contentValues = new ContentValues();
                contentValues.put("sex", "haha");
                database.update("usertb", contentValues, "_id>? and name=?", new String[]{"0", "Joy"});

                Toast.makeText(SqliteActivity.this, "更新数据成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 删除数据
         */
        btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database.execSQL("delete from usertb where name='July';");

                database.delete("usertb", "_id>?", new String[]{"2"});

                Toast.makeText(SqliteActivity.this, "删除数据成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 查询数据
         */
        btnQuery.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Log.i("sqlite", "查询全部数据");
                Cursor cursor = database.rawQuery("select * from usertb;", null);
                if (cursor != null){
                    while (cursor.moveToNext()){
                        int id = cursor.getInt(cursor.getColumnIndex("_id"));
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                        int age = cursor.getInt(cursor.getColumnIndex("age"));
                        String sex = cursor.getString(cursor.getColumnIndex("sex"));

                        Log.i("sqlite", "id = "+id+"; name = "+name+"; age = "+age+"; sex = "+sex);
                    }
                    cursor.close();
                }

                Log.i("sqlite", "查询部分数据");
                Cursor cursor1 = database.query("usertb",
                        new String[]{"_id","name","age","sex"},
                        "_id>?",new String[]{"1"},null,null,"age");
                if (cursor1 != null){
                    while (cursor1.moveToNext()){
                        int id = cursor1.getInt(cursor1.getColumnIndex("_id"));
                        String name = cursor1.getString(cursor1.getColumnIndex("name"));
                        int age = cursor1.getInt(cursor1.getColumnIndex("age"));
                        String sex = cursor1.getString(cursor1.getColumnIndex("sex"));

                        Log.i("sqlite", "id = "+id+"; name = "+name+"; age = "+age+"; sex = "+sex);
                    }
                    cursor1.close();
                }

                Toast.makeText(SqliteActivity.this, "查询数据成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 关闭数据库
         */
        btnCloseDb.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database.close();
                Toast.makeText(SqliteActivity.this, "关闭数据库成功", Toast.LENGTH_SHORT).show();
            }
        });

        /**
         * 删除数据库
         */
        btnDeleteDb.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteDatabase("user.db");
                Toast.makeText(SqliteActivity.this, "删除数据库成功", Toast.LENGTH_SHORT).show();
            }
        });
    }
}
